Case Study: Designing a Data Governance Strategy for a Retail Loyalty Program

Welcome! If you're a data engineer, data analyst, or business beginner, this case study will help you understand how retail stores manage customer information securely. We will explain how major retailers track purchases, send discount emails, and protect customer privacy using simple, real-world analogies.

1. Problem Statement & Business Context

Imagine OmniStore, a major national department store chain. OmniStore has a popular Loyalty Program with over 15 million members. When a customer joins, OmniStore collects:

• Customer Profiles: Names, email addresses, phone numbers, and birthdates (to send birthday coupons).
• Loyalty Points balances: How many points they have earned and spent.
• Purchase History: What items they bought, how much they spent, which store location they visited, and what time they shopped.
• Marketing Preferences: Opt-in or opt-out choices (e.g., "Send me SMS text deals, but do not send me emails").

OmniStore's marketing team wants to analyze this data to:

1. Send Personalized Coupons: If a customer frequently buys baby supplies, send them diaper discounts.
2. Reward High Spenders: Identify VIP customers to give them early access to holiday sales.
3. Collaborate with Brands: Share aggregated shopping stats with brands (like Nike or Samsung) to decide which products to stock.

However, OmniStore must manage this data carefully. They need to protect customer privacy and comply with strict consumer protection laws like GDPR (General Data Protection Regulation in Europe) and CCPA/CPRA (California Consumer Privacy Act).

2. Compliance Frameworks: GDPR & CCPA/CPRA

Retail databases must be designed to respect consumer rights. Let's break down the rules in plain English:

A. Consent Management (Opt-In / Opt-Out)

The Analogy: The VIP club guest list preferences. When a guest enters a club, they tell the host at the door: "You can text me about clothing sales, but do not share my phone number with the shoe department." The host writes these rules next to the customer's name. If the shoe department texts that customer anyway, the club has broken the rules and can be fined. Under GDPR and CCPA, a retail customer has the right to decide how their data is used. If they toggle "Do Not Sell My Personal Information" on OmniStore's website, the data system must automatically update, ensuring their profile is excluded from marketing lists and third-party data sharing.

B. The Right to Delete (The "Right to be Forgotten")

Under CCPA/GDPR, if a customer calls OmniStore and says, "Please delete my account and erase all my data," the bank must purge their name, email, and phone number from all databases.

• The Catch: The store must still keep record of the financial transactions for tax and inventory auditing, but those transactions must be completely unlinked from the customer's identity.

3. Retail Loyalty Governance Architecture

To keep customer profiles secure while allowing analysts to build marketing campaigns, OmniStore separates customer identity data from shopping transaction records.

graph TD
    %% Ingestion of Profile and Sales
    subgraph Ingest [Retail Ingestion Boundary]
        POS[Point of Sale Cash Register] -->|Sends Sales JSON| IngestService[Ingestion Gateway]
        Web[Online Loyalty Web Form] -->|Sends Profile JSON| IngestService
    end

    %% Storage & Access separation
    subgraph Lakehouse [Governed Retail Lakehouse]
        IngestService -->|Write Transaction Data| SalesTable[(Bronze Sales Table: Raw Purchases)]
        IngestService -->|Write Profile Data| ProfileTable[(Bronze Profile Table: Raw Customer Info)]

        SalesTable -->|Spark ETL: Remove ID| AnonSales[(Silver Anon Sales: No Names/Emails)]
        ProfileTable -->|Sync Consent Status| ConsentDB[(Consent Tracking DB: Opt-In/Out)]
    end

    %% Access Layer
    subgraph Consumer [Data Consumers]
        AnonSales --> BI[Marketing BI: Purchase Trends]
        ProfileTable --> Ranger{Policy Engine: Apache Ranger & DDM}
        ConsentDB -.->|Informs Access Rules| Ranger

        Ranger -->|Role: Cashier <br> Masked Email| POSScreen[POS Screen: jo**.d**@email.com]
        Ranger -->|Role: Campaign Manager <br> Filtered by Consent = True| EmailSystem[Automated Coupon Email Sender]
    end

    %% Styles
    classDef secure fill:#f1f5f9,stroke:#0f172a,stroke-width:2px;
    classDef policy fill:#fff1f2,stroke:#e11d48,stroke-width:2px;
    classDef storage fill:#eff6ff,stroke:#2563eb,stroke-width:2px;

    class Ingest secure;
    class Ranger policy;
    class SalesTable,ProfileTable,AnonSales,ConsentDB storage;

4. Key Technical Implementations Explained

A. Dynamic Masking for Store Staff

Cashiers at registers need to verify a customer's loyalty profile, but they do not need to see the customer's full, plain-text email address or phone number on their screens.

The Analogy (The Digital Mask): When a cashier looks up a profile, a screen shows: jo**.d**@email.com. The system automatically blacks out the middle letters. Only authorized corporate customer service managers can press a button to decrypt and see the full email for customer disputes.

SQL Masking Policy Example:

Here is how a data engineer writes the email masking policy:

-- Create an email masking policy
CREATE OR REPLACE MASKING POLICY email_mask AS (val string) RETURNS string ->
  CASE
    -- Marketing Managers and System Admins see the full email
    WHEN CURRENT_ROLE() IN ('MARKETING_ADMIN', 'SYSTEM_ADMIN') THEN val

    -- Cashiers and Store Staff see a partially masked version
    WHEN CURRENT_ROLE() = 'STORE_CASHIER' THEN 
      CONCAT(LEFT(val, 2), '***', SUBSTR(val, CHARINDEX('@', val) - 2))

    -- Everyone else sees 'REDACTED'
    ELSE 'REDACTED'
  END;

-- Apply the policy to the email column in the customer directory
ALTER TABLE lakehouse.silver.customer_loyalty_profiles 
  ALTER COLUMN email_address SET MASKING POLICY email_mask;

B. Consent-Filtering Data Pipelines

If a marketing analyst runs a query to extract a list of emails to send a weekly discount code, the query engine must automatically filter out anyone who has opted out of marketing emails.

To enforce this, OmniStore joins the customer profile table with a real-time Consent Registry table:

-- Create a view that automatically filters out opted-out customers
CREATE OR REPLACE VIEW gold.marketing_email_campaign_list AS
SELECT 
  c.customer_id,
  c.first_name,
  c.email_address
FROM silver.customer_loyalty_profiles c
JOIN silver.customer_consent_status consent
  ON c.customer_id = consent.customer_id
WHERE consent.email_opt_in = TRUE
  AND consent.do_not_sell = FALSE;

Why this is powerful: The marketing analyst doesn't have to remember to check consent filters in their SQL query. The underlying view enforces the rules automatically, eliminating the risk of accidental spam or CCPA violations.

C. Data Clean Rooms: Secure Brand Sharing

OmniStore sells products from brands like Nike. Nike wants to know: "Which OmniStore loyalty members who buy Nike shoes also buy activewear?" Nike wants to target these shoppers, but OmniStore cannot simply hand Nike their customer list (which would violate privacy laws).

The Analogy (The Glass-Walled Meeting Room): Imagine OmniStore and Nike representatives walk into a secure glass room. OmniStore brings their customer list, and Nike brings theirs. Both parties put their lists into an automated lockbox inside the room. The lockbox compares the lists and outputs a single card that says: "There are 10,000 customers who shopped at both stores." It does not show the names or contact details of those 10,000 customers. Both parties walk out of the room knowing the overlap size, but neither got to look at the other's private list.

• How it works technically: We use a cloud data clean room (like Snowflake Data Clean Rooms or AWS Clean Rooms). The clean room runs a cryptographic join on both companies' datasets. It allows Nike to run queries to analyze behaviors, but prevents Nike from running SELECT * to copy down customer emails.

D. The Data Catalog & Metric Definitions

In retail, different departments often calculate metrics differently, leading to confusion during executive meetings.

The Analogy (The Store Directory): If you ask three people in a department store, "What is a jacket?", one might say "only coats," another might say "raincoats," and a third might include "sweaters." To run the store smoothly, there must be a central directory defining what counts as a jacket. In data, we must define metrics consistently. For example, what is an "Active Loyalty Member"?

We define it in a central Data Catalog:

• Business Definition: A loyalty member who has made at least one purchase at a physical store or online in the last 90 days.
• Technical Definition (SQL logic): sql count(distinct customer_id) where purchase_date >= current_date() - interval '90 days' By publishing this definition in an enterprise data catalog (like Amundsen or Collibra), every analyst uses the exact same definition, ensuring dashboards match across the entire company.

5. Architectural Evaluation & Trade-offs

Pros

• Customer Trust: Protecting contact details and respecting opt-outs builds long-term customer loyalty.
• Legal Compliance: Automated consent filtering protects OmniStore from million-dollar CCPA/GDPR violation lawsuits.
• Commercial Partnerships: Secure Data Clean Rooms allow OmniStore to monetize their data insights with brands safely without leaking customer profiles.

Cons & Mitigations

• Complex Delete Workflows: Fully deleting a customer's details across backups, archives, and streaming queues (the "Right to be Forgotten") is technically difficult.
  • Mitigation: Use an automated orchestration script (triggered by a customer service ticket) that deletes personal rows from main tables and replaces them with a unique ID placeholder, breaking the link to the original person.
• Data Siloing: Strict controls can make it harder for business teams to explore data and innovate quickly.
  • Mitigation: Create a sandbox database where analysts can work with fully masked, randomized sample data for brainstorming before requesting official production access.

This content is reserved for Premium Members.